Oracle 실습 수업 노트로, 정규형(1~3정규형, BCNF)과 이상현상(삽입/삭제/수정)의 정의, ERD 구성요소(개체·관계·속성), 관계 카디널리티(1:1/1:N/N:M)에 따른 외래키 배치 규칙을 정리한다. 이어서 회원-물건-구매, 회원-서비스-신청, 회사-서비스 관계를 요구사항→ERD→테이블 명세서→SQL 순서로 실습하며 PK/FK 무결성을 검증한다.
DB 엔진
아래 SQL은 Oracle 문법 기준이다 (varchar2, number, timestamp default sysdate). MySQL은 VARCHAR/AUTO_INCREMENT/DEFAULT CURRENT_TIMESTAMP로 표기가 다르다.
오전수업
테이블 설계 → 데이터 중복 최소화 (이상 현상 (삽입 / 삭제 / 갱신 이상) 이 발생할 수 있다)
데이터베이스 정규화 ( 포스팅 자료 )
테이블 분리
NOTE
데이터베이스 정규화
(데이터 중복을 최소화하고, 각각의 속성들이 기본키에 종속적인지를 확인해봐야한다)
1정규형
각각의 속성은 하나의 튜플에 여러개의 값을 가질 수 없다 (원자성을 가짐)
2정규형
기본키에 대해 다른 컬럼의 속성이 종속되지 않을경우 위배된다
ex) memberId(PK), itemId(PK) 2개의 복합키로 기본키를 가질때, 상품명의 경우 item에만 종속되므로 2정규형이 위배된다
3정규형
“A→B , B→C 이면, A→C이다“ 라는 식이 한 테이블내에서 성립된다면 3정규형이 위배된 것이다
A→B 이고, B→C이라면 2개의 테이블로 나누어서 생각해야한다
BCNF는 3정규형을 강화한것
예시를 찾아보는게 더빠를듯하다
NOTE
이상현상)
삽입 이상
튜플에 데이터를 삽입할때 NULL값(즉, 쓸데없는 값까지 추가해야함)이 입력됨
삭제이상
하나의 데이터를 삭제하려다 다른데이터까지 연쇄적으로 삭제됨
수정이상
수정 시 데이터의 일관성이 깨지는 현상이 발생
ERD ( 개체 관계 도식화)
Entity (개체)
독립적으로 표현 가능한 대상
Relation (관계)
1:1
1:N
N:M
Diagram (도식화)
🟧 : 개체 표현 방법
⚪ : 속성 표현 방법
🔶 : 관계
테이블 명세서 ( 테이블 제작 )
1:1 일경우, 외래키를 두 개의 테이블중 아무곳에나 가져도 상관없다
1:N 일경우, 외래키를 N인 테이블에 외래키를 가져야한다
N:M 일경우, 테이블을 중간에 하나 두어서 사용 (관계테이블)
SQL 쿼리문 작성
개발자의 영역
create table tt(id number(3) primary key,m_id varchar(10),item_id varchar(10),constraint Ref_member_id foreign key(m_id) references member(id)on delete cascade,constraint Ref_item_id foreign key(item_id) references item(id)on delete set null);// on delete cascade : 부모 id 값이 삭제되면 tt테이블의 외래키가 걸려있는 테이블의 튜플도 같이 삭제된다 (연쇄적 삭제)// on delete set null : 부모 id 값이 삭제되면 tt테이블의 외래키 속성에 데이터 값이 null로 변경된다/* 1. 외래키는 null 값을 가질 수 있다 2. 외래키는 부모 속성에 대한 unique함만 가지면 되므로 null과는 상관이 없다*/
// 기본키 설정 적용 확인// (member)insert into member values (null, 'a', '1234', default ,default);/*SQL> insert into member values (null, 'a', '1234', default ,default);insert into member values (null, 'a', '1234', default ,default) *1행에 오류:ORA-01400: NULL을 ("SYSTEM"."MEMBER"."ID") 안에 삽입할 수 없습니다*/insert into service values (null, 'name', 'exp', 'dest');/*SQL> insert into service values (null, 'name', 'exp', 'dest');insert into service values (null, 'name', 'exp', 'dest') *1행에 오류:ORA-01400: NULL을 ("SYSTEM"."SERVICE"."ID") 안에 삽입할 수 없습니다*/// 제약조건 확인insert into member values ('test', 'a', '1234', default, default);select * from member;/*SQL> select * from member;ID NAME PASS---------- ---------- ----------ENTER_DATE--------------------------------------------------------------------------- POINT----------test a 123425/01/02 14:44:59.000000 20*/// subscribe 확인insert into member values ('a', 'park', '1111', default, default);insert into service values (1, 'netflix' , '영상', 'seoul');insert into subscribe values (1, 'a', 1);/*SQL> insert into member values ('a', 'park', '1111', default, default);1 개의 행이 만들어졌습니다.SQL> insert into service values (1, 'netflix' , '영상', 'seoul');1 개의 행이 만들어졌습니다.SQL> insert into subscribe values (1, 'a', 1);1 개의 행이 만들어졌습니다.*/insert into subscribe values (2, 'no id', 1);/*SQL> insert into subscribe values (2, 'no id', 1);insert into subscribe values (2, 'no id', 1)*1행에 오류:ORA-02291: 무결성 제약조건(SYSTEM.REF_MEMBER_FK)이 위배되었습니다- 부모 키가없습니다*/insert into subscribe values (3, 'a', 2);/*SQL> insert into subscribe values (3, 'a', 2);insert into subscribe values (3, 'a', 2)*1행에 오류:ORA-02291: 무결성 제약조건(SYSTEM.REF_SERVICE_FK)이 위배되었습니다- 부모 키가없습니다*/
//(회사)insert into company values(1, 'com1', 'seoul', 'busan');insert into company values (2, 'com2', 'busan', 'seoul');insert into company values (3, 'com3', 'china', 'china');//(service)insert into service values (1, 'netflix', '영상', 'seoul');insert into service values (2, 'youtube', '영상', 'busan');insert into service values (3, 'coupang', 'e-커머스', 'suwon');insert into register values (1, 1, 1);//성공insert into register values (2, 1, 2);//오류/*SQL> insert into register values (2, 1, 2);insert into register values (2, 1, 2)*1행에 오류:ORA-00001: 무결성 제약 조건(SYSTEM.SYS_C0011114)에 위배됩니다*/insert into register values (3, 2, 1);//성공
